package com.cennavi.minemap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.cennavi.minemap.bean.SourceBean;
import com.cennavi.minemap.bean.SourceGroupBean;
import com.cennavi.minemap.bean.SourceMergeBean;
import com.cennavi.minemap.bean.UserSourceBean;

@Component
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })
public class SourceDao extends AbstractDao {
	
	String columes = "id, name, code, groupId, icon, source, version, price, type, "
			+ "content, layerDesc, describe, uploadId, createTime, minZoom, maxZoom, isbind";
	
	String sourceTable = "(SELECT " + columes + " FROM MM_DATASOURCE UNION ALL SELECT " + columes + " FROM MM_DATASOURCE_UPLOAD)";
	
	public int insert(final SourceBean sourceBean) {
		jdbcTemplate.setDataSource(pgDataSource);
		final String sql = "INSERT INTO MM_DATASOURCE_UPLOAD "
				+ "(name, icon, groupId, code, price, type, content, layerDesc, uploadId, createTime, source, minzoom, maxzoom,isbind) "
				+ "VALUES (?, ?, ?, ?, 0, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql,
						new String[] { "id" });
				ps.setString(1, sourceBean.getName());
				ps.setString(2, sourceBean.getIcon());
				ps.setInt(3, sourceBean.getGroupId());
				ps.setString(4, sourceBean.getCode());
				ps.setString(5, sourceBean.getType());
				ps.setString(6, sourceBean.getContent());
				ps.setString(7, sourceBean.getLayerDesc());
				ps.setInt(8, sourceBean.getUploadId());
				ps.setString(9, sourceBean.getCreateTime());
				ps.setString(10, sourceBean.getSource());
				ps.setDouble(11, sourceBean.getMinZoom());
				ps.setDouble(12, sourceBean.getMaxZoom());
				ps.setInt(13, sourceBean.getIsBind());
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}
	
	public int update(final SourceBean sourceBean) {
		jdbcTemplate.setDataSource(pgDataSource);
		try {
			String setSql = super.getSetSql(sourceBean);
			String sql = "UPDATE MM_DATASOURCE_UPLOAD SET " + setSql + " WHERE id = ?";
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, sourceBean.getId());
				}
			});
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	public SourceBean queryById(int id){
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT " + columes + " FROM " + sourceTable + " T WHERE id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { id }, new SourceMapper());
	}
	
	public SourceMergeBean queryMergeBySourceid(int id){
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT id, sourceId FROM MM_SOURCE_MERGE WHERE sourceId = ?";
		try{
			return jdbcTemplate.queryForObject(sql, new Object[] { id }, new SourceMergeMapper());
		}
		catch (EmptyResultDataAccessException e) {
            return null;
        }
	}
	
	public List<SourceBean> queryById(Integer[] ids){
		if (ids.length == 0){
			return null;
		}
		jdbcTemplate.setDataSource(pgDataSource);
		NamedParameterJdbcTemplate namedParameterJdbcTemplate =  
				new NamedParameterJdbcTemplate(jdbcTemplate);  
		
		MapSqlParameterSource parameters = new MapSqlParameterSource(); 
        parameters.addValue("ids", Arrays.asList(ids));
		String sql = "SELECT * FROM " + sourceTable + " T WHERE id IN (:ids)";
		return namedParameterJdbcTemplate.query(sql, parameters, new SourceMapper());
	}
	
	public List<SourceBean> queryByUserGroupId(final int userId, int groupId, boolean isBind){
		jdbcTemplate.setDataSource(pgDataSource);
		String where = "";
		if(isBind){
			where = " AND S.isBind = 1 ";
		}
		if(userId > 0){
			String insertSql = "INSERT INTO MM_USER_SOURCE SELECT ?,ID FROM MM_DATASOURCE S WHERE UPLOADID = 0 AND NOT EXISTS (SELECT 1 FROM MM_USER_SOURCE US WHERE S.ID = US.SOURCEID AND US.USERID=?)";
			jdbcTemplate.update(insertSql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, userId);
					ps.setInt(2, userId);
				}
			});
		}
		if(userId == 0 && groupId == 0){
			String sql = "SELECT " + columes + " FROM MM_DATASOURCE S WHERE S.uploadId = 0 " + where + " ORDER BY id";
			return (List<SourceBean>)jdbcTemplate.query(sql, new SourceMapper());
		}
		else if(userId > 0 && groupId > 0){
			String sql = "SELECT " + columes + " FROM " + sourceTable + " S, MM_USER_SOURCE U WHERE S.id = U.sourceId AND userId = ? AND groupId = ? " + where + " ORDER BY id";
			return (List<SourceBean>)jdbcTemplate.query(sql, new Object[] { userId, groupId }, new SourceMapper());
		}
		else if(userId == 0 && groupId > 0){
			String sql = "SELECT " + columes + " FROM MM_DATASOURCE S WHERE S.groupId = ? AND S.uploadId = 0 " + where + " ORDER BY id";
			return (List<SourceBean>)jdbcTemplate.query(sql, new Object[] { groupId }, new SourceMapper());
		}
		else if(userId > 0 && groupId == 0){
			String sql = "SELECT " + columes + " FROM MM_DATASOURCE S, MM_USER_SOURCE U WHERE S.uploadId = 0 AND S.id = U.sourceId AND userId = ? " + where + " ORDER BY id";
			return (List<SourceBean>)jdbcTemplate.query(sql, new Object[] { userId }, new SourceMapper());
		}
		else{
			return null;
		}
	}
	
	public List<SourceBean> querySelfByUserGroupId(final int userId){
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT " + columes + " FROM MM_DATASOURCE_UPLOAD S, MM_USER_SOURCE U WHERE S.id = U.sourceId AND userId = ? AND S.uploadId > 0";
		return (List<SourceBean>)jdbcTemplate.query(sql, new Object[] { userId }, new SourceMapper());
	}
	
	public String queryTileJsonById(int id){
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT tileJson FROM " + sourceTable + " T WHERE id = ?";
		return (String)jdbcTemplate.queryForObject(sql, new Object[] { id }, String.class);
	}
	
	public String querySourceLayerDescById(int id){
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT layerDesc FROM " + sourceTable + " T WHERE id = ?";
		try{
			return (String)jdbcTemplate.queryForObject(sql, new Object[] { id }, String.class);
		}
		catch(Exception e){
			return null;
		}
	}
	
	public int sourceBuy(UserSourceBean userSourceBean){
		try{
			jdbcTemplate.setDataSource(pgDataSource);

			jdbcTemplate.execute("begin");

			List<Integer> sourceIds = userSourceBean.getSourceIds();
			for (int sourceId : sourceIds) {
				String delete_sql = "DELETE FROM MM_USER_SOURCE WHERE userId = ? AND sourceId = ?";
				jdbcTemplate.update(delete_sql, new Object[] { userSourceBean.getUserId(), sourceId });
				String insert_sql = "INSERT INTO MM_USER_SOURCE (userId, sourceId) VALUES(?, ?)";
				jdbcTemplate.update(insert_sql, new Object[] { userSourceBean.getUserId(), sourceId });
			}
			jdbcTemplate.execute("commit");
			return 0;
		}
		catch(Exception e){
			jdbcTemplate.execute("rollback");
			e.printStackTrace();
			return -1;
		}
	}
	
	public int insertUserSource(UserSourceBean userSourceBean){
		try{
			jdbcTemplate.setDataSource(pgDataSource);
			String sql = "INSERT INTO MM_USER_SOURCE (userId, sourceId) VALUES(?, ?)";
			List<Integer> sourceIds = userSourceBean.getSourceIds();
			for (int sourceId : sourceIds) {
				jdbcTemplate.update(sql, new Object[] { userSourceBean.getUserId(), sourceId });
			}
			return 0;
		}
		catch(Exception e){
			e.printStackTrace();
			return -1;
		}
	}

	public List<SourceGroupBean> queryGroup(boolean showUpload){
		String where = "";
		if(!showUpload){
			where = " WHERE S.uploadId = 0 ";
		}
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = 
				  "SELECT G.ID, G.NAME, S.COUNT              "
				+ "  FROM MM_SOURCE_GROUP G,                 "
				+ "       (SELECT groupId, COUNT(1) AS COUNT "
				+ "          FROM " + sourceTable + " S      "
				+ where
				+ "         GROUP BY groupId) S              "
				+ " WHERE G.id = S.groupId ORDER BY G.id     ";
		return (List<SourceGroupBean>)jdbcTemplate.query(sql, new SourceGroupMapper());
	}
	
	public List<SourceGroupBean> queryGroupByUserId(int userId){
		jdbcTemplate.setDataSource(pgDataSource);		
		String sql = 
			    "SELECT G.ID, G.NAME, S.COUNT              "
			  + "  FROM MM_SOURCE_GROUP G,                 "
			  + "       (SELECT groupId, COUNT(1) AS COUNT "
			  + "          FROM " + sourceTable + " S      "
			  + "         WHERE S.uploadId = 0             "
			  + "           AND EXISTS (SELECT 1           "
			  + "                  FROM MM_USER_SOURCE U   "
			  + "                 WHERE U.userId = ?       "
			  + "                   AND U.sourceId = S.id) "
			  + "         GROUP BY groupId) S              "
			  + " WHERE G.id = S.groupId ORDER BY G.id     " ;
		return (List<SourceGroupBean>)jdbcTemplate.query(sql, new Object[]{ userId }, new SourceGroupMapper());
	}
	
	
	protected class SourceMapper implements RowMapper<SourceBean> {   
        public SourceBean mapRow(ResultSet rs, int rowNum) throws SQLException {   
        	SourceBean source = new SourceBean();
        	source.setId(rs.getInt("id"));
        	source.setName(rs.getString("name"));
        	source.setCode(rs.getString("code"));
        	source.setGroupId(rs.getInt("groupId"));
        	source.setIcon(rs.getString("icon"));
        	source.setSource(rs.getString("source"));
        	source.setVersion(rs.getString("version"));
        	source.setPrice(rs.getDouble("price"));
        	source.setType(rs.getString("type"));
        	source.setContent(rs.getString("content"));
        	source.setLayerDesc(rs.getString("layerDesc"));
        	source.setDescribe(rs.getString("describe"));
        	source.setUploadId(rs.getInt("uploadId"));
        	source.setCreateTime(rs.getString("createTime"));
        	source.setMinZoom(rs.getDouble("minZoom"));
        	source.setMaxZoom(rs.getDouble("maxZoom"));
        	source.setIsBind(rs.getInt("isBind"));
			return source;
        }   
    }
	
	public List<SourceBean> queryUploadSourceByUserId(int userId){
		jdbcTemplate.setDataSource(pgDataSource);
		String source = userId + " upload";
		String sql = "SELECT " + columes + " FROM mm_datasource_upload T WHERE source = ?";
		return jdbcTemplate.query(sql, new Object[] { source }, new SourceMapper());
	}
	
	protected class SourceGroupMapper implements RowMapper<SourceGroupBean> {   
        public SourceGroupBean mapRow(ResultSet rs, int rowNum) throws SQLException {   
        	SourceGroupBean group = new SourceGroupBean();
        	group.setId(rs.getInt("id"));
        	group.setName(rs.getString("name"));
        	group.setCount(rs.getInt("count"));
			return group;
        }   
    }
	
	protected class SourceMergeMapper implements RowMapper<SourceMergeBean> {   
        public SourceMergeBean mapRow(ResultSet rs, int rowNum) throws SQLException {   
        	SourceMergeBean merge = new SourceMergeBean();
        	merge.setId(rs.getString("id"));
        	merge.setSourceId(rs.getInt("sourceId"));
			return merge;
        }   
    }
}
